﻿using JiaBao.Core;
using JiaBao.Services;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace JiaBao.Plugin.Import
{
    /// <summary>
    /// p_Import.xaml 的交互逻辑
    /// </summary>
    public partial class p_Import : Page
    {
        public p_Import()
        {
            InitializeComponent();

            var dt = SqlHelper.ExecuteDataTable("select * from Auctionall where 拍卖会ID>0 order by 拍卖会ID desc");
            cmb_Auction.ItemsSource = dt.DefaultView;
            cmb_Auction.DisplayMemberPath = "拍卖会名称";
            cmb_Auction.SelectedValuePath = "拍卖会ID";


        }

        private void btn_FilePath_Click(object sender, RoutedEventArgs e)
        {
            txt_FilePath.Text = CommonHelper.OpenFileDialog("Excel|*.xls");
        }

        private void btn_Import_Click(object sender, RoutedEventArgs e)
        {
            var filePath = txt_FilePath.Text.Trim();
            if (!File.Exists(filePath))
                return;


            HSSFWorkbook wk = null;
            using (FileStream fs = File.OpenRead(filePath))   //打开myxls.xls文件 
                wk = new HSSFWorkbook(fs);  //把xls文件中的数据写入wk中


            for (int i = 0; i < wk.Count; i++)
            {
                Import(wk, i);
            }
            cmb_Auction.SelectedIndex = -1;
            txt_FilePath.Text = "";
            MessageBox.Show("导入完成");
        }

        private void Import(HSSFWorkbook wk, int index)
        {
            Dictionary<string, int> excelFilds = new Dictionary<string, int>();
            try
            {
               // 检测文件路径 

                ISheet sheet = wk.GetSheetAt(index);   //读取当1sheet

            #region 读取表头

            //读取第一行 表头
            var row1 = sheet.GetRow(0);
            for (int i = 0; i < row1.Count(); i++)
            {
                var s = row1.Cells[i].ToString().Trim();
                if (string.IsNullOrEmpty(s))
                    continue;

                excelFilds.Add(s, i);   //保存列序号名称
            }

            #endregion 

            #region 拼接语句

            string sql = string.Empty, col = string.Empty, val = string.Empty;
            //遍历 保存的字段标识
            foreach (KeyValuePair<string, int> item in excelFilds)
            {
                col += "," + item.Key;
                val += "," + "@val" + item.Key ;
            } 

            col += ",拍卖会id";
            val += ",@aucid";


            col = col.Substring(1);
            val = val.Substring(1);

            switch (index)
            {
                case 0:
                    sql = string.Format("insert into storeall ({0}) values ({1})", col, val);
                    break;
                case 1:
                    sql = string.Format("insert into Salerall ({0}) values ({1})", col, val);
                    break;
                case 2:
                    sql = string.Format("insert into Jtdjall ({0}) values ({1})", col, val);
                    break;
                default:
                    return;

            } 
            #endregion

            #region 循环写入数据库

            for (int i = 1; i <= sheet.LastRowNum; i++)  //LastRowNum 是当前表的总行数
            {
                List<SqlParameter> parameter = new List<SqlParameter>();

                IRow row = sheet.GetRow(i);  //读取当前行数据

                #region 检测

                if (null == row)//空行
                {
                    continue;
                } 
                #endregion
                var s = sql;
                //遍历 保存的字段标识
                foreach (KeyValuePair<string, int> item in excelFilds)
                {
                    ICell cell = row.GetCell(item.Value);
                    //s = s.Replace ("@val" + item.Key, null == cell ? "''" : "'"+ cell.ToString()+"'");
                    parameter.Add(new SqlParameter("val" + item.Key, null == cell ? "" : cell.ToString()));
                }

                //s = s.Replace("@aucid" , cmb_Auction.SelectedValue.ToString ());
                //SqlHelper.ExecuteNonQuery(s);

                parameter.Add(new SqlParameter("aucid", cmb_Auction.SelectedValue));
                SqlHelper.ExecuteNonQuery(sql, parameter.ToArray());

            } 
                #endregion 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_u_Click(object sender, RoutedEventArgs e)
        {
            SqlHelper.ExecuteNonQuery("alter table storeall alter column 尺寸cm nvarchar(300)");
            MessageBox.Show("ok");
        }
    }
}
